﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Catering_Rules
{
    public class ViewOperaction
    {
        #region 属性注入
        private readonly Catering_Operation.ViewOperaction dal = new Catering_Operation.ViewOperaction();
        #endregion

        public DataSet GetViewToDataSet(string strViewName)
        {
            return dal.GetViewToDataSet(strViewName);
        }
        public DataSet GetViewToDataSet(string strViewName, string strWhere)
        {
            return dal.GetViewToDataSet(strViewName, strWhere);
        }
        public Int32 GetStrsql(string strViewName)
        {
            return dal.GetStrsql(strViewName);
        }
        public Int32 GetSingle(string strTableName, string strColumName, string strWhere = null)
        {
            return dal.GetSingle(strTableName, strColumName, strWhere);
        }
        public string GetSingle_str(string strTableName, string strColumName, string strWhere = null)
        {
            return dal.GetSingle_str(strTableName, strColumName, strWhere);
        }
        public Boolean DataTableInsertToServer(DataTable dt, string strName, DataColumnCollection dtColum)
        {
            return dal.DataTableInsertToServer(dt, strName, dtColum);
        }

        public DataSet GetKcpd(string whereClause, DateTime datetime, string orderby)
        {
            return dal.GetKcpd(whereClause, datetime, orderby);
        }

        public decimal GetKuCunAmontMoneyByMonth(string strGroupID, string strClassID, DateTime datetime, string strMaxType, string strMinType, string kufang)
        {
            int GroupID = 0;
            int ClassID = 0;
            string whereClause = "";
            Int32.TryParse(strGroupID, out GroupID);
            Int32.TryParse(strClassID, out ClassID);
            if (GroupID > 0 && ClassID > 0 && datetime > DateTime.MinValue)
            {
                DateTime minDate = DateTime.Parse(datetime.ToShortDateString()).AddMonths(-1);
                whereClause = string.Format(" AND fr.GroupID={0} AND fr.ClassID={1} AND fr.OutDate BETWEEN '" + minDate.Year + "-" + minDate.Month + "-26' AND  '" + datetime.Year + "-" + datetime.Month + "-25' ", GroupID, ClassID);
            }
            if (strMaxType != "-10")
            {
                if (strMinType != "-10")
                {
                    whereClause += " and vw.MaterialsID=" + strMaxType + " and vw.MaterialsInfoID =" + strMinType;
                }
                else
                {
                    whereClause += " and vw.MaterialsID=" + strMaxType;
                }
            }
            if (kufang != "-10")
            {
                whereClause += " and vw.StorageID=" + kufang;
            }
            return dal.GetKuCunAmontMoneyByMonth(whereClause);
        }

        #region 报表
        /// <summary>
        /// 消耗分类汇总数据
        /// </summary>
        /// <returns></returns>
        public DataSet GetViewSumToDataSet()
        {
            return dal.GetViewSumToDataSet();
        }

        /// <summary>
        /// 累计百分比数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetViewTotalToDataTable()
        {
            return dal.GetViewTotalToDataTable();
        }

        /// <summary>
        /// 人均消耗分类汇总
        /// </summary>
        /// <returns></returns>
        public DataTable GetPersonTypeSumByProc_PersonType()
        {
            return dal.GetPersonTypeSumByProc_PersonType();
        }

        /// <summary>
        /// 月累计百分比
        /// </summary>
        /// <returns></returns>
        public DataTable GetMonthTotalByProc_TotalMonth(string month)
        {
            return dal.GetMonthTotalByProc_TotalMonth(month);
        }

        /// <summary>
        /// 人均食用油统计
        /// </summary>
        /// <returns></returns>
        public DataTable GetPersonEdibleByProc_PersonEdibleStatistical(string Date)
        {
            return dal.GetPersonEdibleByProc_PersonEdibleStatistical(Date);
        }


        /// <summary>
        /// 食用油统计——明细
        /// </summary>
        /// <returns></returns>
        public DataSet GetView_EdibleStatistics_ListToDataSet()
        {
            return dal.GetView_EdibleStatistics_ListToDataSet();
        }


        /// <summary>
        /// 食用油统计——全年
        /// </summary>
        /// <returns></returns> 
        public DataTable GetEdibleStatistics_YearByProc_YearEdible()
        {
            return dal.GetEdibleStatistics_YearByProc_YearEdible();
        }

        /// <summary>
        /// 食用油月对比
        /// </summary>
        /// <returns></returns>
        public DataTable GetEdibleStatistics_MonthByProc_EdibleMonth(string Month, string CName)
        {
            return dal.GetEdibleStatistics_MonthByProc_EdibleMonth(Month, CName);
        }

        /// <summary>
        /// 食用油消耗分类汇总
        /// </summary>
        /// <param name="areaName"></param>
        /// <param name="cName"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetXiaohaoSumByprocEdibleConSum(string areaName, string cName, string month)
        {
            // 得到新数据后，如何来处理这些数据，只能拿到1到12 月份的数据
            DataTable dt = dal.GetXiaohaoSumByprocEdibleConSum(areaName, cName, month);
            ///

            //var dtinfo = from dtinfos in dt.AsEnumerable()
            //             where dtinfos.Field<Int32>("") < 7
            //             select dtinfos.Field<Int32>("");
            //dtinfo.Sum();

            // 上半年合计
            //var table = from t in dt.AsEnumerable()
            //            where t.Field<DateTime>("Month").Equals("2012-1-1")

            //            group t by t.Field<string>("Name0") into s
            //            select new
            //            {
            //                Name0 = s.Key,
            //                Name1 = s.Sum(v => v.Field<int>("消耗量")),
            //                Name2 = s.Sum(v => v.Field<decimal>("标准值")),
            //                Name3 = s.Sum(v => v.Field<int>("要料量")),
            //                Name4 = s.Sum(v => v.Field<float>("就餐人数")),
            //                Name5 = s.Sum(v => v.Field<decimal>("超量")),
            //                Name6 = s.Sum(v => v.Field<int>("人均值")),
            //                Name7 = s.Sum(v => v.Field<int>("Month"))
            //            };
            ///

            DataRow dr1 = dt.NewRow();
            #region——上半年合计
            if (dt.Rows.Count == 12)
            {
                dr1[0] = "上半年合计";
                // 得到上半年的总和
                for (int i = 0; i < dt.Rows.Count - 6; i++)
                {
                    if (i == 0)
                    {
                        dr1 = dt.Rows[i];
                    }
                    else
                    {
                        DataRow dr = dt.Rows[i];
                        // 列的循环
                        for (int j = 1; j < dt.Columns.Count; j++)
                        {
                            decimal dec = Convert.ToDecimal(dr[j]);
                            decimal dec1 = Convert.ToDecimal(dr1[j]);
                            dr1[j] = dec + dec1;
                        }
                    }
                }
                dt.Rows.Add(dr1);       // 上半年的总和
            }
            #endregion

            #region——下半年合计
            //得到下半年的总和
            for (int i = 6; i < dt.Rows.Count; i++)
            {
                dr1[0] = "下半年合计";
                if (i == 6)
                {
                    dr1 = dt.Rows[i];
                }
                else
                {
                    DataRow dr = dt.Rows[i];
                    // 列的循环
                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        decimal dec = Convert.ToDecimal(dr[j]);
                        decimal dec1 = Convert.ToDecimal(dr1[j]);
                        dr1[j] = dec + dec1;
                    }
                }
            }
            DataRow drs = dt.NewRow();
            ///上半年
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    drs[0] = "上半年小计"; 
            //    decimal? yaoyaoliang = null ;
            //    decimal? xiaohaoliang = null ;
            //    decimal? biaozhunzhi = null ;
            //    for (int j = 1; j < dt.Rows.Count-6; j++)
            //    {

            //        yaoyaoliang = yaoyaoliang + (decimal)dt.Rows[j][i];
            //        //drs[1] = yaoyaoliang;
            //        xiaohaoliang = xiaohaoliang + (decimal)dt.Rows[j][i+1];

            //        biaozhunzhi = biaozhunzhi + (decimal)dt.Rows[j][i + 1];

            //       /// double xiaohaoliang = 
            //        ///drs[2] = 

            //    }

            //    drs[i] = yaoyaoliang;
            //}

            ///
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    for (int j = 6; j < dt.Rows.Count ; j++)
            //    {
            //        double yaoyaoliang = 0.0d;
            //        yaoyaoliang = yaoyaoliang + (double)dt.Rows[0][1];
            //    }
            //}

            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    for (int j = 0; j < dt.Rows.Count; j++)
            //    {
            //        double yaoyaoliang = 0.0d;
            //        yaoyaoliang = yaoyaoliang + (double)dt.Rows[0][1];
            //    }
            //}


            #endregion

            #region——全年合计
            // 得到总的合计
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dr1[0] = "合计";
                if (i == 12)
                {
                    dr1 = dt.Rows[i];
                }
                else
                {
                    DataRow dr = dt.Rows[i];
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        decimal dec = Convert.ToDecimal(dr[j]);
                        decimal dec1 = Convert.ToDecimal(dr1[j]);
                        dr1[j] = dec + dec1;
                    }
                }
            }
            #endregion

            return dt;
        }

        public DataSet GetcaigoujihuaToDataSet(string strViewName)
        {
            return dal.GetcaigoujihuaToDataSet(strViewName);
        }

        #endregion


        /// <summary>
        /// 食用油月对比--视图查询
        /// </summary>
        /// <returns></returns>
        public DataSet GetEdibleDuibiByView()
        {
            return dal.GetEdibleDuibiByView();
        }

        /// <summary>
        /// 食用油消耗分类汇总
        /// </summary>
        /// <param name="areaName"></param>
        /// <param name="cName"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetXiaohaoSumByprocEdibleConSum(int isAll, string areaName, string cName, DateTime stare, DateTime end)
        {
            // 得到新数据后，如何来处理这些数据，只能拿到1到12 月份的数据
            DataTable dt = dal.GetXiaohaoSumByprocEdibleConSum(isAll, areaName, cName, stare, end);
            ///

            //var dtinfo = from dtinfos in dt.AsEnumerable()
            //             where dtinfos.Field<Int32>("") < 7
            //             select dtinfos.Field<Int32>("");
            //dtinfo.Sum();

            // 上半年合计
            //var table = from t in dt.AsEnumerable()
            //            where t.Field<DateTime>("Month").Equals("2012-1-1")
            //            group t by t.Field<string>("Name0") into s
            //            select new
            //            {
            //                Name0 = s.Key,
            //                Name1 = s.Sum(v => v.Field<int>("消耗量")),
            //                Name2 = s.Sum(v => v.Field<decimal>("标准值")),
            //                Name3 = s.Sum(v => v.Field<int>("要料量")),
            //                Name4 = s.Sum(v => v.Field<float>("就餐人数")),
            //                Name5 = s.Sum(v => v.Field<decimal>("超量")),
            //                Name6 = s.Sum(v => v.Field<int>("人均值")),
            //                Name7 = s.Sum(v => v.Field<int>("Month"))
            //            };
            ///

            DataRow dr1 = dt.NewRow();
            #region——上半年合计
            if (dt.Rows.Count == 12)
            {
                dr1[0] = "上半年合计";
                // 得到上半年的总和
                for (int i = 0; i < dt.Rows.Count - 6; i++)
                {
                    if (i == 0)
                    {
                        dr1 = dt.Rows[i];
                    }
                    else
                    {
                        DataRow dr = dt.Rows[i];
                        // 列的循环
                        for (int j = 1; j < dt.Columns.Count; j++)
                        {
                            decimal dec = Convert.ToDecimal(dr[j]);
                            decimal dec1 = Convert.ToDecimal(dr1[j]);
                            dr1[j] = dec + dec1;
                        }
                    }
                }
                dt.Rows.Add(dr1);       // 上半年的总和
            }
            #endregion

            #region——下半年合计
            //得到下半年的总和
            for (int i = 6; i < dt.Rows.Count; i++)
            {
                dr1[0] = "下半年合计";
                if (i == 6)
                {
                    dr1 = dt.Rows[i];
                }
                else
                {
                    DataRow dr = dt.Rows[i];
                    // 列的循环
                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        decimal dec = Convert.ToDecimal(dr[j]);
                        decimal dec1 = Convert.ToDecimal(dr1[j]);
                        dr1[j] = dec + dec1;
                    }
                }
            }
            DataRow drs = dt.NewRow();
            ///上半年
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    drs[0] = "上半年小计"; 
            //    decimal? yaoyaoliang = null ;
            //    decimal? xiaohaoliang = null ;
            //    decimal? biaozhunzhi = null ;
            //    for (int j = 1; j < dt.Rows.Count-6; j++)
            //    {

            //        yaoyaoliang = yaoyaoliang + (decimal)dt.Rows[j][i];
            //        //drs[1] = yaoyaoliang;
            //        xiaohaoliang = xiaohaoliang + (decimal)dt.Rows[j][i+1];

            //        biaozhunzhi = biaozhunzhi + (decimal)dt.Rows[j][i + 1];

            //       /// double xiaohaoliang = 
            //        ///drs[2] = 

            //    }

            //    drs[i] = yaoyaoliang;
            //}

            ///
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    for (int j = 6; j < dt.Rows.Count ; j++)
            //    {
            //        double yaoyaoliang = 0.0d;
            //        yaoyaoliang = yaoyaoliang + (double)dt.Rows[0][1];
            //    }
            //}

            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    for (int j = 0; j < dt.Rows.Count; j++)
            //    {
            //        double yaoyaoliang = 0.0d;
            //        yaoyaoliang = yaoyaoliang + (double)dt.Rows[0][1];
            //    }
            //}


            #endregion

            #region——全年合计
            // 得到总的合计
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dr1[0] = "合计";
                if (i == 12)
                {
                    dr1 = dt.Rows[i];
                }
                else
                {
                    DataRow dr = dt.Rows[i];
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        decimal dec = Convert.ToDecimal(dr[j]);
                        decimal dec1 = Convert.ToDecimal(dr1[j]);
                        dr1[j] = dec + dec1;
                    }
                }
            }
            #endregion

            return dt;
        }

    }
}
